package com.faker.dba.service.impl;

import com.faker.dba.model.dto.GeTableInfoDto;
import com.faker.dba.model.dto.GeTableListDto;
import com.faker.dba.model.dto.GetDatabaseInfoDto;
import com.faker.dba.model.vo.LayuiColVo;
import com.faker.dba.model.vo.LayuiPageDataVo;
import com.faker.dba.service.DataBaseService;
import com.faker.dba.util.Toolkit;
import com.faker.dba.wrapper.AesWrapMapper;
import com.faker.dba.wrapper.Wrapper;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * <p>
 * 数据库 服务实现类
 * </p>
 *
 * @author anlinxi
 * @since 2022-08-21
 */
@Slf4j
@Service
public class DataBaseServiceImpl implements DataBaseService {

    @Value("${spring.datasource.driver-class-name}")
    private String datasourceDriver;


    @ApiModelProperty("数据源")
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取数据库信息
     *
     * @param params
     * @return
     */
    @Override
    public Wrapper getDatabaseInfo(GetDatabaseInfoDto params) {
        String dbType = null;
        if (datasourceDriver.contains("mysql")) {
            dbType = "mysql";
        } else if (datasourceDriver.contains("oracle")) {
            dbType = "oracle";
        } else {
            return AesWrapMapper.error("未定义的数据库类型[" + datasourceDriver + "]");
        }
        StringBuffer sql = new StringBuffer();
        List<String> tablesSet = new ArrayList<String>();
        if ("oracle".equals(dbType)) {
            sql.append("select table_name from user_tables order by table_name");
//            List<Map<String, Object>> tablesList = jdbcTemplate.queryForList(sql.toString());
//            for (Map<String, Object> table : tablesList) {
//                if (null != table.get("TABLE_NAME")) {
//                    String tableName = String.valueOf(table.get("TABLE_NAME"));
//                    tablesSet.add(tableName);
//                }
//            }
        } else if ("mysql".equals(dbType)) {
            sql.append("show tables");
        }
        List<Map<String, Object>> tablesList = jdbcTemplate.queryForList(sql.toString());
        for (Map<String, Object> table : tablesList) {
            //Map 遍历
            for (Map.Entry<String, Object> entry : table.entrySet()) {
                if (null != entry.getValue()) {
                    String tableName = String.valueOf(entry.getValue());
                    tablesSet.add(tableName);
                }
            }
        }
        Collections.sort(tablesSet);
        return AesWrapMapper.ok(tablesSet);
    }

    /**
     * 获取表结构信息
     *
     * @param params
     * @return
     */
    @Override
    public Wrapper<List<LayuiColVo>> geTableInfo(GeTableInfoDto params) {
        StringBuffer sql = new StringBuffer();
        if (datasourceDriver.contains("mysql")) {
            sql.append("SELECT\n" +
                    "\tCOLUMN_NAME COLUMN_NAME,\n" +
                    "\tCOLUMN_COMMENT COMMENTS,\n" +
                    "\tDATA_TYPE \n" +
                    "FROM\n" +
                    "\tINFORMATION_SCHEMA.COLUMNS \n" +
                    "WHERE\n" +
                    "\ttable_name = ? \n" +
                    "\tAND table_schema = DATABASE ()");
        } else if (datasourceDriver.contains("oracle")) {
            sql.append("select ucc.COLUMN_NAME, atc.DATA_TYPE, ucc.COMMENTS\n  from user_col_comments ucc\n  left join user_tab_cols atc\n    on ucc.table_name = atc.table_name and ucc.column_name = atc.column_name\n where ucc.table_name = ?");
        } else {
            return AesWrapMapper.error("未定义的数据库类型[" + datasourceDriver + "]");
        }
        List<Map<String, Object>> tablesList = jdbcTemplate.queryForList(sql.toString(), params.getTableName());
        List<LayuiColVo> layuiColVoList = new ArrayList<>();

        for (Map<String, Object> table : tablesList) {
            layuiColVoList.add(new LayuiColVo(table.get("COLUMN_NAME"), table.get("COMMENTS"), table.get("DATA_TYPE")));
        }
        //fixed: 'right', title:'操作', width: 125, minWidth: 125, toolbar: '#barDemo'
        LayuiColVo toolbar = new LayuiColVo();
        toolbar.setFixed("right");
        toolbar.setTitle("操作");
        toolbar.setWidth(125);
        toolbar.setMinWidth(125);
        if (1 == 2) {
            LayuiColVo rowid = new LayuiColVo();
            rowid.setTitle("ROWID");
            rowid.setField("ROWID");
            rowid.setHide(true);
            layuiColVoList.add(rowid);
            //oracle的有rowid支持更新删除操作
            toolbar.setToolbar("#toolbarUpdate");
            layuiColVoList.add(toolbar);
        } else {
            toolbar.setToolbar("#toolbarLook");
            layuiColVoList.add(toolbar);
        }
        return AesWrapMapper.ok(layuiColVoList);
    }

    /**
     * 获取表数据信息
     *
     * @param params
     * @return
     */
    @Override
    public LayuiPageDataVo geTableList(GeTableListDto params) {
        LayuiPageDataVo layuiPageVo = new LayuiPageDataVo();
        if (StringUtils.isBlank(params.getTableName())) {
            layuiPageVo.setMsg("表名[" + datasourceDriver + "]不能为空");
            return layuiPageVo;
        }
        StringBuffer sql = new StringBuffer();
        List<Map<String, Object>> tablesList = new ArrayList<>();
        Integer count = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM " + params.getTableName(), Integer.class);
        layuiPageVo.setCount(count);
        log.info("limit=" + params.getLimit() + "; page=" + params.getPage());
        if (datasourceDriver.contains("mysql")) {
            Long start = (params.getPage() - 1) * params.getLimit() + 1;
            sql.append("SELECT * FROM ").append(params.getTableName()).append(" LIMIT ").append(start).append(",").append(params.getLimit());
        } else if (datasourceDriver.contains("oracle")) {
            //每页开始的条数：start = (pageIndex-1) * pageSize + 1
            Long start = (params.getPage() - 1) * params.getLimit() + 1;
            //每页结束的条数：end = pageIndex * pageSize
            Long end = params.getPage() * params.getLimit();
            sql.append("SELECT * FROM (SELECT T.*,ROWNUM RN FROM ").append(params.getTableName()).append(" T) B WHERE B.RN BETWEEN ").append(start).append(" AND ").append(end);
        } else {
            layuiPageVo.setMsg("未定义的数据库类型[" + datasourceDriver + "]");
            return layuiPageVo;
        }
        log.info(sql.toString());
        tablesList = jdbcTemplate.queryForList(sql.toString());
        for (Map<String, Object> map : tablesList) {
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                entry.setValue(Toolkit.getString(entry.getValue()));
            }
        }
        layuiPageVo.setData(tablesList);
        layuiPageVo.setCode(0);
        layuiPageVo.setMsg("查询完毕");
        return layuiPageVo;
    }
}
